package com.ponxu.mb.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.ponxu.db.SqlBuilder;
import com.ponxu.db.SqlRunner;
import com.ponxu.db.wrap.RowWrapper;
import com.ponxu.db.wrap.impl.IntegerRowWrapper;
import com.ponxu.mb.Global;
import com.ponxu.mb.db.ConnectionManager;
import com.ponxu.mb.model.Category;
import com.ponxu.mb.model.Post;
import com.ponxu.utils.Lang;

/**
 * 分类/标签
 * 
 * @author xwz
 * 
 */
public class CategoryService {
	public static String CATEGORY_TABLE = Global.read("DB_TABLE_PREFIX")
			+ "category";

	public static String POST_CATEGORY_TABLE = Global.read("DB_TABLE_PREFIX")
			+ "post_category";

	private static String fullCols = "*";
	private static RowWrapper<Category> fullRowWrapper = new RowWrapper<Category>() {
		public Category wrap(ResultSet rs) throws SQLException {
			Category cat = new Category();
			cat.setId(rs.getInt("id"));
			cat.setName(rs.getString("name"));
			cat.setSeoName(rs.getString("seo_name"));
			cat.setType(rs.getString("type"));
			cat.setPostCount(rs.getInt("post_count"));
			cat.setParentId(rs.getInt("pid"));
			cat.setLevel(rs.getString("level"));
			return cat;
		}
	};

	public static final List<String> TYPES = Arrays.asList(new String[] {
			"category", "tag" });

	/**
	 * 单一查询
	 * 
	 * @param where
	 * @param params
	 * @return
	 */
	public static Category getUni(String where, Object... params) {
		SqlRunner runner = new SqlRunner(ConnectionManager.get());
		SqlBuilder.SELECT(fullCols);
		SqlBuilder.FROM(CATEGORY_TABLE);
		SqlBuilder.WHERE(where);
		return runner.selectOne(SqlBuilder.SQL(), fullRowWrapper, params);
	}

	/**
	 * 多个查询
	 * 
	 * @param where
	 * @param params
	 * @return
	 */
	public static List<Category> searchList(String where, String order,
			Object... params) {
		SqlRunner runner = new SqlRunner(ConnectionManager.get());
		SqlBuilder.SELECT(fullCols);
		SqlBuilder.FROM(CATEGORY_TABLE);
		SqlBuilder.WHERE(where);
		SqlBuilder.ORDER_BY(order);
		return runner.select(SqlBuilder.SQL(), fullRowWrapper, params);
	}

	/**
	 * 根据seo name查询
	 * 
	 * @param seoName
	 * @return
	 */
	public static Category getBySeoName(String seoName) {
		String where = "seo_name=?";
		return getUni(where, seoName);
	}

	/**
	 * 根据名字查询某类下的一个
	 * 
	 * @return
	 */
	public static Category getByNameAndType(String name, String type) {
		String where = "name=? AND type=?";
		return getUni(where, name, type);
	}

	public static Category getCatByName(String name) {
		return getByNameAndType(name, "category");
	}

	public static Category getTagByName(String name) {
		return getByNameAndType(name, "tag");
	}

	public static Category getById(int id) {
		String where = "id=?";
		return getUni(where, id);
	}

	public static List<Category> searchAll() {
		return searchList(null, null);
	}

	public static List<Category> searchAllCategory() {
		String where = "type='category'";
		String order = "level";
		return searchList(where, order);
	}

	public static List<Category> searchAllTag() {
		String where = "type='tag'";
		return searchList(where, null);
	}

	/**
	 * 保存 分类或者标签
	 * 
	 * @param cat
	 */
	public static int add(Category cat) {
		SqlRunner runner = new SqlRunner(ConnectionManager.get());

		SqlBuilder.INSERT_INTO(CATEGORY_TABLE);
		SqlBuilder.VALUES("name, seo_name, type, pid", "?, ?, ?, ?");

		int id = runner.insert(SqlBuilder.SQL(), cat.getName(),
				cat.getSeoName(), cat.getType(), cat.getParentId());
		cat.setId(id);

		// 分类目录具有层级
		if ("category".equals(cat.getType())) {
			String level = String.valueOf(id);
			if (cat.getParentId() != 0) {
				// 有父节点
				Category parent = getById(cat.getParentId());
				level = parent.getLevel() + "-" + id;
			}

			cat.setLevel(level);
			updateOnly(cat);// 效率低-点
		}

		return id;
	}

	/**
	 * 更新 分类或者标签
	 * 
	 * @param cat
	 */
	public static int update(Category cat) {
		int i = updateOnly(cat);
		// 分类目录具有层级
		if ("category".equals(cat.getType())) {
			String level = String.valueOf(cat.getId());
			if (cat.getParentId() != 0) {
				// 有父节点
				Category parent = getById(cat.getParentId());
				level = parent.getLevel() + "-" + cat.getId();
			}
			cat.setLevel(level);
			updateOnly(cat);// 效率低-点
		}
		return i;
	}

	private static int updateOnly(Category cat) {
		SqlRunner runner = new SqlRunner(ConnectionManager.get());
		SqlBuilder.UPDATE(CATEGORY_TABLE);
		SqlBuilder.SET("name=?, seo_name=?, type=?, pid=?, level=?");
		SqlBuilder.WHERE("id=?");
		return runner.execute(SqlBuilder.SQL(), cat.getName(),
				cat.getSeoName(), cat.getType(), cat.getParentId(),
				cat.getLevel(), cat.getId());
	}

	/**
	 * 删除
	 * 
	 * @param ids
	 * @return
	 */
	public static int delete(int[] ids) {
		SqlRunner runner = new SqlRunner(ConnectionManager.get());

		String in = SqlBuilder.inClause(ids);

		// 检查是否被层级应用
		SqlBuilder.SELECT("id");
		SqlBuilder.FROM(CATEGORY_TABLE);
		SqlBuilder.WHERE("pid in " + in);
		Integer ret = runner.selectOne(SqlBuilder.SQL(),
				IntegerRowWrapper.getInstance());
		if (ret != null && ret > 0) {
			return 0;
		}

		// 清除关系
		SqlBuilder.DELETE_FROM(POST_CATEGORY_TABLE);
		SqlBuilder.WHERE("category_id in " + in);
		runner.execute(SqlBuilder.SQL());

		// 删除分类目录实体
		SqlBuilder.DELETE_FROM(CATEGORY_TABLE);
		SqlBuilder.WHERE("id in " + in);
		return runner.execute(SqlBuilder.SQL());
	}

	/**
	 * 关联关系
	 * 
	 * @param id
	 * @param cats
	 */
	public static void relative(int postId, List<Category> cats) {
		if (cats == null)
			return;

		// 效率低-点
		for (Category c : cats) {
			int id = c.getId();
			if (id == 0) {
				Category temp = getByNameAndType(c.getName(), c.getType());
				if (temp == null) {
					// 不存, 保存
					id = add(c);
				} else {
					// 已有此标签
					id = temp.getId();
				}
				c.setId(id);
			}
			addRelationship(postId, c.getId());
		}
	}

	// 添加关系
	private static int addRelationship(int postId, int categoryId) {
		SqlRunner runner = new SqlRunner(ConnectionManager.get());
		SqlBuilder.INSERT_INTO(POST_CATEGORY_TABLE);
		SqlBuilder.VALUES("post_id, category_id", "?, ?");
		int i = runner.execute(SqlBuilder.SQL(), postId, categoryId);

		// 计数+1
		SqlBuilder.UPDATE(CATEGORY_TABLE);
		SqlBuilder.SET("post_count=post_count+1");
		SqlBuilder.WHERE("id=?");
		runner.execute(SqlBuilder.SQL(), categoryId);

		return i;
	}

	/**
	 * 清理关联关系
	 * 
	 * @param id
	 * @param cats
	 */
	public static int clearRelationship(int[] postIds) {
		SqlRunner runner = new SqlRunner(ConnectionManager.get());
		String in = SqlBuilder.inClause(postIds);

		// 计数-1
		SqlBuilder.UPDATE(CATEGORY_TABLE + " a, ");
		SqlBuilder.UPDATE(POST_CATEGORY_TABLE + " b");
		SqlBuilder.SET("a.post_count=a.post_count-1");
		SqlBuilder.WHERE("a.id=b.category_id");
		SqlBuilder.WHERE("b.post_id in " + in);
		runner.execute(SqlBuilder.SQL());

		// 真实清理
		SqlBuilder.DELETE_FROM(POST_CATEGORY_TABLE);
		SqlBuilder.WHERE("post_id in " + in);
		return runner.execute(SqlBuilder.SQL());
	}

	/**
	 * 给文章列表设置 分类/标签 信息
	 * 
	 * @param postIds
	 * @return
	 */
	public static List<Category> populateForPosts(List<Post> list) {
		if (Lang.isEmpty(list))
			return null;

		StringBuilder postIds = new StringBuilder("(");
		final Map<Integer, Post> postMapWithId = new HashMap<Integer, Post>();
		for (int i = 0, len = list.size(); i < len; i++) {
			if (i != 0) {
				postIds.append(",");
			}
			Post p = list.get(i);
			postIds.append(p.getId());
			postMapWithId.put(p.getId(), p);
		}
		postIds.append(")");

		SqlBuilder.SELECT("a.post_id, b.*");
		SqlBuilder.FROM(POST_CATEGORY_TABLE + " a");
		SqlBuilder.FROM(CATEGORY_TABLE + " b");
		SqlBuilder.WHERE("a.category_id=b.id");
		SqlBuilder.WHERE("a.post_id IN " + postIds.toString());

		SqlRunner runner = new SqlRunner(ConnectionManager.get());
		return runner.select(SqlBuilder.SQL(), new RowWrapper<Category>() {
			public Category wrap(ResultSet rs) throws SQLException {
				Category cat = fullRowWrapper.wrap(rs);
				int postId = rs.getInt("post_id");
				Post post = postMapWithId.get(postId);
				if ("tag".equals(cat.getType())) {
					List<Category> tags = post.getTags();
					if (tags == null) {
						tags = new ArrayList<Category>();
						post.setTags(tags);
					}
					tags.add(cat);
				} else if ("category".equals(cat.getType())) {
					List<Category> cats = post.getCats();
					if (cats == null) {
						cats = new ArrayList<Category>();
						post.setCats(cats);
					}
					cats.add(cat);
				}
				return cat;
			}
		});
	}

	/**
	 * 给文章设置 分类/标签 信息
	 */
	public static void populateForPost(Post post) {
		List<Post> temp = new ArrayList<Post>();
		temp.add(post);
		populateForPosts(temp);
	}
}
